
package com.acxiom.bsh.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLType;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.InvalidResultSetAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.acxiom.bsh.dao.LuckydrawDao;
import com.acxiom.bsh.logger.LoggerManager;
import com.acxiom.bsh.pojo.CardPool;
import com.acxiom.bsh.pojo.Prize;
import com.acxiom.bsh.pojo.PrizeRecord;
import com.acxiom.bsh.pojo.SParameter;

@Repository
public class LuckydrawDaoImpl implements LuckydrawDao {

	@Autowired
	JdbcTemplate jdbcTemplate;

	@Override
	public int getCurrentDayDrawTimes(String mixNick) {
		try {
			String sql = "select count(1) as num from prizerecord where mixNick=? and prizeDateTime > date_format(now(),'%y-%m-%d')";

			SqlRowSet set = jdbcTemplate.queryForRowSet(sql, mixNick);
			if (set.next()) {
				return set.getInt("num");
			}
		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getCurrentDayDrawTimes Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getCurrentDayDrawTimes  DataAccess Error:", e);
		}

		return 0;
	}

	@Override
	public int getCurrentDay1234PrizeTimes(String mixNick, int prizeId) {
		int count = 0;
		try {
			String sql = "select count(1) as num from prizerecord where mixNick=? and  prizeId =?";

			count = jdbcTemplate.queryForObject(sql, new Object[] { mixNick, prizeId }, Integer.class);

		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getCurrentDay1234PrizeTimes Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getCurrentDay1234PrizeTimes  DataAccess Error:", e);
		}

		return count;
	}

	@Override
	public List<Prize> getPrize() {

		String sql = "select id,giftName,giftDescription,prizeLevel,minTimes,probability,currentInventory,usedInventory,totalInventory," + "creationDateTime,updateDateTime,creationUID,updateUID,flag,giftType,pointCode,needPurchase from prize where flag=1 order by prizeLevel asc";

		List<Prize> prizeList = null;
		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql);

			prizeList = new ArrayList<Prize>();
			while (set.next()) {
				Prize prize = new Prize();
				prize.setId(set.getInt("id"));
				prize.setGiftName(set.getString("giftName"));
				prize.setPrizeLevel(set.getInt("prizeLevel"));
				prize.setMinTimes(set.getInt("minTimes"));
				prize.setProbability(set.getDouble("probability"));
				prize.setCurrentInventory(set.getInt("currentInventory"));
				prize.setUsedInventory(set.getInt("usedInventory"));
				prize.setTotalInventory(set.getInt("totalInventory"));
				prize.setGiftType(set.getInt("giftType"));
				prize.setPointCode(set.getString("pointCode"));
				prize.setNeedPurchase(set.getInt("needPurchase"));
				prizeList.add(prize);
			}
		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getPrize  ResultSetAccess Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getPrize  DataAccess Error:", e);
		}
		return prizeList;
	}

	@Override
	public Prize getDefaultPrize() {

		String sql = "select id,giftName,giftDescription,prizeLevel,minTimes,probability,currentInventory,usedInventory,totalInventory," + "creationDateTime,updateDateTime,creationUID,updateUID,flag,giftType,pointCode,needPurchase from prize where flag=1 and defaultFlag = 1";

		Prize prize = null;
		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql);

			prize = new Prize();
			if (set.next()) {
				prize.setId(set.getInt("id"));
				prize.setGiftName(set.getString("giftName"));
				prize.setPrizeLevel(set.getInt("prizeLevel"));
				prize.setMinTimes(set.getInt("minTimes"));
				prize.setProbability(set.getDouble("probability"));
				prize.setCurrentInventory(set.getInt("currentInventory"));
				prize.setUsedInventory(set.getInt("usedInventory"));
				prize.setTotalInventory(set.getInt("totalInventory"));
				prize.setGiftType(set.getInt("giftType"));
				prize.setPointCode(set.getString("pointCode"));
				prize.setNeedPurchase(set.getInt("needPurchase"));
			}
		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getDefaultPrize  ResultSet Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getDefaultPrize  DataAccess Error:", e);
		}
		return prize;
	}

	@Override
	public int getTotalDrawTimes(String mixNick) {
		String sql = "select count(1) as num from prizerecord where mixNick=?";

		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql, mixNick);
			if (set.next()) {
				return set.getInt("num");
			}
		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getTotalDrawTimes  ResultSet Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getTotalDrawTimes  DataAccess Error:", e);
		}

		return 0;
	}

	@Override
	public int updateInventory(Prize prize) {
		int result = 0;
		try {
			String sql = "update prize set currentInventory=currentInventory-1,usedInventory=usedInventory+1,updateDateTime=now() where id=? and currentInventory > 0";

			result = jdbcTemplate.update(sql, prize.getId());
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("updateInventory  DataAccess Error:", e);
		}
		return result;
	}

	@Override
	public int savePrizeRecord(PrizeRecord record) {
		int result = 0;
		try {
			String sql = "INSERT INTO bsh_tmall.prizerecord(mixNick,prizeId,prizeName,prizeDateTime,sellerName,creationDateTime,updateDateTime,creationUID,updateUID,status)" + " VALUES(?,?,?,now(),?,now(),now(),'lucky_draw','lucky_draw',?)";

			result = jdbcTemplate.update(sql, record.getMixNick(), record.getPrizeId(), record.getPrizeName(), record.getSellerName(),record.getStatus());
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("savePrizeRecord  DataAccess Error:", e);
		}
		return result;
	}

	@Override
	public List<PrizeRecord> getPrizeRecord(String mixNick) {

		String sql = "select *  from (SELECT a.id,mixNick,prizeId,prizeName,prizeDateTime,prizeLevel " + "FROM prizerecord a join prize b on a.prizeId=b.id where mixNick=?  UNION ALL" + " SELECT a.id,mixNick,prizeId,prizeName,prizeDateTime,prizeLevel "
				+ "FROM double11prizerecord a join double11prize b on a.prizeId=b.id where mixNick=? ) T order by prizeDateTime desc";

		List<PrizeRecord> prizeList = null;
		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql, mixNick, mixNick);

			prizeList = new ArrayList<PrizeRecord>();
			while (set.next()) {
				PrizeRecord prize = new PrizeRecord();
				prize.setId(set.getInt("id"));
				prize.setMixNick(set.getString("mixNick"));
				prize.setPrizeId(set.getInt("prizeId"));
				prize.setPrizeLevel(set.getInt("prizeLevel"));
				prize.setPrizeName(set.getString("prizeName"));
				prize.setPrizeDateTime(set.getDate("prizeDateTime"));

				prizeList.add(prize);
			}
		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getPrizeRecord  ResultSet Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getPrizeRecord  DataAccess Error:", e);
		}
		return prizeList;
	}

	@Override
	public List<PrizeRecord> getPrizeListTop5() {
		String sql = "SELECT a.id,mixNick,prizeId,prizeName,prizeDateTime,prizeLevel FROM prizerecord a  join  prize b  on a.prizeId =b.id  where  b.prizeLevel in (1,2,3)   order by prizeLevel asc LIMIT  0,5";

		List<PrizeRecord> prizeList = null;
		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql);

			prizeList = new ArrayList<PrizeRecord>();
			while (set.next()) {
				PrizeRecord prize = new PrizeRecord();
				prize.setId(set.getInt("id"));
				prize.setMixNick(set.getString("mixNick"));
				prize.setPrizeId(set.getInt("prizeId"));
				prize.setPrizeLevel(set.getInt("prizeLevel"));
				prize.setPrizeName(set.getString("prizeName"));
				prize.setPrizeDateTime(set.getDate("prizeDateTime"));

				prizeList.add(prize);
			}
		} catch (InvalidResultSetAccessException e) {
			e.printStackTrace();
			LoggerManager.getErrorLogger().error("getPrizeRecordTop5  ResultSet Error:", e);
		} catch (DataAccessException e) {
			e.printStackTrace();
			LoggerManager.getErrorLogger().error("getPrizeRecordTop5  DataAccess Error:", e);
		}
		return prizeList;
	}

	@Override
	public CardPool getCard() {
		String sql = "select * from cardpool where usedFlag = 0  limit 1";

		CardPool card = null;
		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql);

			card = new CardPool();
			if (set.next()) {
				card.setId(set.getInt("id"));
				card.setCardNo(set.getString("cardNo"));
				card.setUsedFlag(set.getInt("usedFlag"));
				card.setCreationDt(set.getDate("creationDt"));
				card.setUpdateDt(set.getDate("updateDt"));
			}
		} catch (InvalidResultSetAccessException e) {
			LoggerManager.getErrorLogger().error("getCard  ResultSet Error:", e);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("getCard  DataAccess Error:", e);
		}
		return card;
	}

	@Override
	public String updateUsedCard(final String mixNick) {
		String result = null;
		try {
			final String procsql = "{CALL PROC_GenerateCardNo(?,?)}";
			
			result = jdbcTemplate.execute(new CallableStatementCreator() {

				@Override
				public CallableStatement createCallableStatement(Connection con) throws SQLException {
					CallableStatement cs = con.prepareCall(procsql);  
					cs.setString(1, mixNick);// 设置输入参数的值
					cs.registerOutParameter(2,Types.VARCHAR);// 注册输出参数的类型
					return cs;
				}
			}, new CallableStatementCallback<String>() {
				@Override
				public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
					cs.execute();
	                return cs.getString(2);// 获取输出参数的值   
				}

			});
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("updateInventory  DataAccess Error:", e);
		}
		return result;
	}

	@Override
	public SParameter getSParameter(String paramCode) {
		SParameter parameter = null;
		String sql = "select * from sparameter where code=?";
		parameter = jdbcTemplate.queryForObject(sql, new RowMapper<SParameter>() {

			@Override
			public SParameter mapRow(ResultSet rs, int rowNum) throws SQLException {
				SParameter sParameter = new SParameter();
				sParameter.setCode("code");
				sParameter.setValue(rs.getString("value"));

				return sParameter;
			}

		}, paramCode);
		return parameter;
	}

	@Override
	public PrizeRecord getPrizeRecordByLLt(PrizeRecord record) {
		String sql = "SELECT * FROM prizerecord where prizeId=? and mixNick=? and status=0";

		PrizeRecord prizeRecord = null;
		try {
			SqlRowSet set = jdbcTemplate.queryForRowSet(sql, record.getPrizeId(), record.getMixNick());

			prizeRecord = new PrizeRecord();
			while (set.next()) {
				prizeRecord.setId(set.getInt("id"));
				prizeRecord.setMixNick(set.getString("mixNick"));
				prizeRecord.setPrizeId(set.getInt("prizeId"));
				prizeRecord.setStatus(0);

			}
		} catch (InvalidResultSetAccessException e) {
			e.printStackTrace();
			LoggerManager.getErrorLogger().error("getPrizeRecord  ResultSet Error:", e);
		} catch (DataAccessException e) {
			e.printStackTrace();
			LoggerManager.getErrorLogger().error("getPrizeRecord  DataAccess Error:", e);
		}
		return prizeRecord;
	}

	@Override
	public int updatePrizeStatus(int pid, int status,String mixNick) {
		int result = 0;
		try {
			String sql = "update prizerecord set status = ? where prizeId = ? and mixNick = ?";

			result = jdbcTemplate.update(sql, status,pid,mixNick);
		} catch (DataAccessException e) {
			LoggerManager.getErrorLogger().error("updateInventory  DataAccess Error:", e);
		}
		return result;
	}

}
